package database;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Vector;

import object.Role;
import object.Users;
import objectView.UserAuthorization;
import utility.StringUtil;
import clientMain.Login;
import clientMain.Main;

public class UserDelegate {
	/**
	 * @author Tidus
	 * @param userName
	 * @param userPwd
	 * @category check login
	 * @return
	 */
	public int loginCheck(String userName, String userPwd){
        try{
        	Login.conn.setAutoCommit(false);
            CallableStatement cst = Login.conn.prepareCall("{call sp_CheckLogin(?,?,?)}");
            cst.setString(1, StringUtil.setFilterIllegal(userName));
            cst.setString(2, userPwd);
            //	Lay bien tra ve cua Procedure trong SQL
            cst.registerOutParameter(3, Types.INTEGER);            
            cst.executeUpdate();
            Login.conn.commit();
            int flag = cst.getInt(3);
            return flag;
        }catch (Exception e) {
        	e.printStackTrace();
        	return 0;
        }
    }
	
	public boolean changePwd(String userName, String userPwdNew){
		try{
        	Login.conn.setAutoCommit(false);
            CallableStatement cst = Login.conn.prepareCall("{call sp_ChangePwd(?,?)}");
            cst.setString(1, StringUtil.setFilterIllegal(userName));
            cst.setString(2, StringUtil.setFilterIllegal(userPwdNew));         
            cst.executeUpdate();
            Login.conn.commit();
            return true;
        }catch (Exception e) {
        	e.printStackTrace();
        	return false;
        }
	}
	
	/**
	 * @author Tidus
	 * @param userName
	 * @return danh sách quyền hạn của user.
	 */
	public Vector<UserAuthorization> getListAuthorizations(String userName){
		Vector<UserAuthorization> list = new Vector<UserAuthorization>();
		try {
			Login.conn.setAutoCommit(false);
			CallableStatement cst = Login.conn.prepareCall("{call sp_ListAuthorizations(?)}");
			cst.setString(1, userName);
			ResultSet rs = cst.executeQuery();
			while(rs.next()){
				UserAuthorization userAuthorization = new UserAuthorization();
				userAuthorization.setUserId(rs.getInt("UserId"));
				userAuthorization.setUserName(StringUtil.getFilterIllegal(rs.getString("UserName")));
				userAuthorization.setUserPwd(rs.getString("UserPassword"));
				userAuthorization.setUserInfo(rs.getString("UserInfo"));
				userAuthorization.setUserInsert(rs.getString("UserInsert"));
				userAuthorization.setLvlValue(rs.getInt("Lvl_Value"));
				userAuthorization.setRoleName(StringUtil.getFilterIllegal(rs.getString("Role_Name")));
				userAuthorization.setPermId(rs.getInt("Perm_Id"));
				userAuthorization.setPermName(StringUtil.getFilterIllegal(rs.getString("Perm_Name")));
				userAuthorization.setModuleId(rs.getInt("Module_Id"));
				userAuthorization.setModuleName(StringUtil.getFilterIllegal(rs.getString("Module_Name")));
				userAuthorization.setModuleDes(StringUtil.getFilterIllegal(rs.getString("Module_Describe")));
				userAuthorization.setModuleDep(rs.getInt("Module_Department"));
				list.add(userAuthorization);
			}
			Login.conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			list = null;
		}
		return list;
	}
	
	/**
	 * @author Tidus
	 * @param userName
	 * @category kiểm tra sự tồn tại của User Name
	 * @return true: exists, false: not exists 
	 */
	public boolean isExistsUserName(String userName){
		try{
        	Login.conn.setAutoCommit(false);
            CallableStatement cst = Login.conn.prepareCall("{call sp_CheckUserName(?,?)}");
            cst.setString(1, StringUtil.setFilterIllegal(userName));
            //	Lay bien tra ve cua Procedure trong SQL
            cst.registerOutParameter(2, Types.INTEGER);            
            cst.executeUpdate();
            Login.conn.commit();
            int flag = cst.getInt(2);
            if(flag == 1) return true;
            else return false;
        }catch (Exception e) {
        	e.printStackTrace();
        	return true;
        }
	}
	
	/**
	 * @author Tidus
	 * @param emId
	 * @category ktra su ton tai cua userInfo
	 * @return true: exists, false: not exists
	 */
	public boolean isExistsUserInfo(String emId){
		try{
        	Login.conn.setAutoCommit(false);
            CallableStatement cst = Login.conn.prepareCall("{call sp_CheckUserInfo(?,?)}");
            cst.setString(1, emId);
            //	Lay bien tra ve cua Procedure trong SQL
            cst.registerOutParameter(2, Types.INTEGER);            
            cst.executeUpdate();
            Login.conn.commit();
            int flag = cst.getInt(2);
            if(flag == 1) return true;
            else return false;
        }catch (Exception e) {
        	e.printStackTrace();
        	return true;
        }
	}
	
	/**
	 * @author Tidus
	 * @param roleName
	 * @category kiểm tra sự tồn tại của Role Name
	 * @return true: exists, false: not exists 
	 */
	public boolean isExistsRoleName(String roleName){
		try{
        	Login.conn.setAutoCommit(false);
            CallableStatement cst = Login.conn.prepareCall("{call sp_CheckRoleName(?,?)}");
            cst.setString(1, StringUtil.setFilterIllegal(roleName));
            //	Lay bien tra ve cua Procedure trong SQL
            cst.registerOutParameter(2, Types.INTEGER);            
            cst.executeUpdate();
            Login.conn.commit();
            int flag = cst.getInt(2);
            if(flag == 1) return true;
            else return false;
        }catch (Exception e) {
        	e.printStackTrace();
        	return true;
        }
	}
	
	public boolean createAccount(Users user, Role role, String listRMP){
		try {
			Login.conn.setAutoCommit(false);
			CallableStatement cst = Login.conn.prepareCall("{call sp_CreateUser(?,?,?,?,?,?,?,?)}");
			cst.setString(1, StringUtil.setFilterIllegal(user.getUserName()));
			cst.setString(2, user.getUserPassword());
			cst.setString(3, user.getUserInfo());
			cst.setInt(4, Main.userId);
			cst.setInt(5, user.getLvlValue());
			cst.setString(6, StringUtil.setFilterIllegal(role.getRoleName()));
			cst.setString(7, StringUtil.setFilterIllegal(role.getRoleDescribe()));
			cst.setString(8, listRMP);
			cst.executeUpdate();
			Login.conn.commit();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean editAcc(Users user, Role role, String listRMPAdd, String listRMPDel){
		try {
			Login.conn.setAutoCommit(false);
			CallableStatement cst = Login.conn.prepareCall("{call sp_UpdateUser(?,?,?,?,?,?,?,?)}");
			cst.setString(1, StringUtil.setFilterIllegal(user.getUserName()));
			cst.setInt(2, Main.userId);
			cst.setInt(3, user.getLvlValue());
			cst.setInt(4, role.getRoleId());
			cst.setString(5, StringUtil.setFilterIllegal(role.getRoleName()));
			cst.setString(6, StringUtil.setFilterIllegal(role.getRoleDescribe()));
			cst.setString(7, listRMPAdd);
			cst.setString(8, listRMPDel);
			cst.executeUpdate();
			Login.conn.commit();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public Vector<Users> getlistAcc(int userId, int lvlValue){
		Vector<Users> list = new Vector<Users>();
		try {
			Login.conn.setAutoCommit(false);
			CallableStatement cst = Login.conn.prepareCall("{call sp_ListUser(?,?)}");
			cst.setInt(1, userId);
			cst.setInt(2, lvlValue);
			ResultSet rs = cst.executeQuery();			
			while(rs.next()){
				Users user = new Users();
				user.setUserId(rs.getInt("UserId"));
				user.setUserName(StringUtil.getFilterIllegal(rs.getString("UserName")));
				list.add(user);
			}
			Login.conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			list = null;
		}
		return list;
	}
	
	public UserAuthorization getAccInfo(int userId){
		UserAuthorization user = new UserAuthorization();
		try {
			Login.conn.setAutoCommit(false);
			CallableStatement cst = Login.conn.prepareCall("{ call sp_UserInfo(?)}");
			cst.setInt(1, userId);
			ResultSet rs = cst.executeQuery();
			while (rs.next()) {
				user.setUserId(rs.getInt("UserId"));
				user.setUserName(StringUtil.getFilterIllegal(rs.getString("UserName")));
				user.setUserInfo(rs.getString("UserInfo"));
				user.setUserInsert(StringUtil.getFilterIllegal(rs.getString("UserInsert")));
				user.setLvlValue(rs.getInt("Lvl_Value"));
				user.setRoleId(rs.getInt("Role_Id"));
				user.setRoleName(StringUtil.getFilterIllegal(rs.getString("Role_Name")));
				user.setRoleDes(StringUtil.getFilterIllegal(rs.getString("Role_Describe")));
			}
			Login.conn.commit();
			return user;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	
}
